﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace TDLib.DB
{
    public class SQLiteHelper
    {
        private string m_dbconnstr;

        public SQLiteHelper(string dbfilepath)
        {
            m_dbconnstr = "Data Source=" + dbfilepath;
        }

        /// <summary>
        /// 从数据库中执行某一SQL命令，不返回数据集合(用指定的连接名)
        /// </summary>
        public int RunSQL(string strSQL)
        {
            int intResult = 0;

            SQLiteConnection conn = new SQLiteConnection(m_dbconnstr);
            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand(strSQL, conn);

            try
            {
                intResult = cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }            

            return intResult;
        }

        /// <summary>
        /// 从数据库中执行某一SQL命令，不返回数据集合(用指定的连接名)
        /// </summary>
        public int RunSQL(SQLiteCommand cmd)
        {
            int intResult = 0;

            SQLiteConnection conn = new SQLiteConnection(m_dbconnstr);
            conn.Open();

            cmd.Connection = conn;

            try
            {
                intResult = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }       

            conn.Close();

            return intResult;
        }

        /// <summary>
        /// 执行sql语句，并返回dataset
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public DataSet RunSQLDS(string strSQL)
        {
            SQLiteConnection conn = new SQLiteConnection(m_dbconnstr);

            SQLiteCommand cmd = new SQLiteCommand(strSQL, conn);
            SQLiteDataAdapter dad = new SQLiteDataAdapter(cmd);

            DataSet ds = new DataSet();

            dad.Fill(ds);

            return ds;

        }

        /// <summary>
        /// 执行sql语句，并返回dataset
        /// </summary>
        public DataSet RunSQLDS(SQLiteCommand cmd)
        {
            SQLiteConnection conn = new SQLiteConnection(m_dbconnstr);

            cmd.Connection = conn;
            SQLiteDataAdapter dad = new SQLiteDataAdapter(cmd);

            DataSet ds = new DataSet();

            dad.Fill(ds);

            return ds;

        }
    }
}
